package test;

import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

import org.junit.Test;

import sqlengine.Main;

public class Select {

	static int score = 0;
	
	@Test
	public void basicSelect(){
		boolean eCaught = false;
		String query = "create table simple1 (f1 varchar2(10) primary key, f2 varchar2(15) );\n";
		String out = null;
		try{
			Main m = new Main();
			out = m.process(query);
			out = m.process("insert into simple1(f1,f2) values ('abc', 'efg');\n");
			out = m.process("select f1 from simple1;\n");
			
			if(out == null || !out.split("\nplan:")[0].equals("1:\nf1: abc\n"))
				throw new Exception();	
			
			out = m.process("select f2, simple1.f1 from simple1;\n");
			
			if(out == null || !out.split("\nplan:")[0].equals("1:\nf2: efg\nsimple1.f1: abc\n"))
				throw new Exception();
			
			out = m.process("select * from simple1;\n");
			
			if(out == null || !out.split("\nplan:")[0].equals("1:\nf1: abc\nf2: efg\n"))
				throw new Exception();
			
			out = m.process("insert into simple1(f1,f2) values ('abcd', 'efg');\n");
			
			out = m.process("select f2 from simple1;\n");
			if(out == null || !out.split("\nplan:")[0].equals("1:\nf2: efg\n\n2:\nf2: efg\n"))  //removed ;
				throw new Exception();	
					
		}catch(Exception e){
			eCaught = true;
		}
		if(!eCaught){
			score+= Breakdown.sBasic;
			SC.score.append("\n\t select on a single table without where clause works - " + Breakdown.sBasic + "/" + Breakdown.sBasic);
		}else
			SC.score.append("\n\t select on a single table without where clause doesn't work - 0/" + Breakdown.sBasic);
		
	}
	
	
	@Test 
	public void oneTableSelectWithWhere(){
		boolean eCaught = false;
		String out = null;
		try{
			Main m = new Main();
			m.process("@select.sql");
			
			out = m.process("select yEAr from movie where dir = 'Steven Spielberg';\n");
			
			Set<Map<String, String>> ref = new HashSet<Map<String, String>>();
			Set<Map<String, String>> actual;
			actual = parseResults(out);
			
			Map<String, String> tmp = new HashMap<String, String>();
			tmp.put("year", "1981");
			ref.add(tmp);
			tmp = new HashMap<String, String>();
			tmp.put("year", "1998");
			ref.add(tmp);
			tmp = new HashMap<String, String>();
			tmp.put("year", "2001");
			ref.add(tmp);
			tmp = new HashMap<String, String>();
			tmp.put("year", "2002");
			ref.add(tmp);
			
			if(!actual.equals(ref))
				throw new Exception();
			
			out = m.process("select title, dir from movie where year = movie.id;\n");
			
			actual = parseResults(out);
			ref.clear();
			
			tmp = new HashMap<String, String>();
			tmp.put("title", "Star Wars");
			tmp.put("dir", "George Lucas");
			ref.add(tmp);
			tmp = new HashMap<String, String>();
			tmp.put("title", "Raiders of the Lost Ark");
			tmp.put("dir", "Steven Spielberg");
			ref.add(tmp);
			tmp = new HashMap<String, String>();
			tmp.put("title", "Citizen Kane");
			tmp.put("dir", "Orson Welles");
			ref.add(tmp);
			
			if(!actual.equals(ref))
				throw new Exception();
			
			
			out = m.process("select * from cast where title = 'The Shawshank Redemption' and actor = 'Tim Robbins' and  cast.title= 'The Shawshank Redemption';\n");
			
			actual = parseResults(out);
			
			ref.clear();
			tmp = new HashMap<String, String>();
			tmp.put("title", "The Shawshank Redemption");
			tmp.put("actor", "Tim Robbins");
			tmp.put("id", "010040e");
			ref.add(tmp);
			
			if(!actual.equals(ref))
				throw new Exception();
					
		}catch(Exception e){
			eCaught = true;
		}
		
		
		if(!eCaught){
			score+= Breakdown.s1tabWhere;
			SC.score.append("\n\t select on a single table with where clause works - " + Breakdown.s1tabWhere + "/" + Breakdown.s1tabWhere);
		}else
			SC.score.append("\n\t select on a single table with where clause doesn't work - 0/" + Breakdown.s1tabWhere);
	}
	
	@Test 
	public void twoTableSelect(){
		boolean eCaught = false;
		String query1 = "create table simple1 (f1 varchar2(10), f2 varchar2(15) primary key);\n";
		String query2 = "create table simple2 (f1 varchar2(10), f2 varchar2(15) primary key, f3 varchar2(15));\n";
		String out = null;
		try{
			Main m = new Main();
			out = m.process(query1);
			out = m.process(query2);
			out = m.process("insert into simple1(f1,f2) values ('abc', 'efg');\n");
			out = m.process("insert into simple2(f1,f2, f3) values ('ab', 'ef', 'ijk');\n");
			
			out = m.process("select * from simple1, simple2;\n");
			if(out == null || !out.split("\nplan:")[0].equals("1:\nf1: abc\nf2: efg\nf1: ab\nf2: ef\nf3: ijk\n"))
				throw new Exception();	
			
			out = m.process("insert into simple1(f1,f2) values ('abcd', 'efgh');\n");
			out = m.process("select f3 from simple1, simple2;\n");
			if(out == null || !out.split("\nplan:")[0].equals("1:\nf3: ijk\n\n2:\nf3: ijk\n"))
				throw new Exception();	
			
			out = m.process("insert into simple2(f1,f2, f3) values ('x', 'y', 'z');\n");
			
			out = m.process("select simple2.f1 from simple1, simple2;\n");
			if(!(  out.split("\nplan:")[0].equals("1:\nsimple2.f1: ab\n\n2:\nsimple2.f1: ab\n\n3:\nsimple2.f1: x\n\n4:\nsimple2.f1: x\n")
				|| out.split("\nplan:")[0].equals("1:\nsimple2.f1: ab\n\n2:\nsimple2.f1: x\n\n3:\nsimple2.f1: ab\n\n4:\nsimple2.f1: x\n")
				|| out.split("\nplan:")[0].equals("1:\nsimple2.f1: ab\n\n2:\nsimple2.f1: x\n\n3:\nsimple2.f1: x\n\n4:\nsimple2.f1: ab\n")
				|| out.split("\nplan:")[0].equals("1:\nsimple2.f1: x\n\n2:\nsimple2.f1: ab\n\n3:\nsimple2.f1: ab\n\n4:\nsimple2.f1: x\n")
				|| out.split("\nplan:")[0].equals("1:\nsimple2.f1: x\n\n2:\nsimple2.f1: ab\n\n3:\nsimple2.f1: x\n\n4:\nsimple2.f1: ab\n")
				|| out.split("\nplan:")[0].equals("1:\nsimple2.f1: x\n\n2:\nsimple2.f1: x\n\n3:\nsimple2.f1: ab\n\n4:\nsimple2.f1: ab\n")))
				throw new Exception();
			
			out = m.process("select simple1.f3 from simple1, simple2;\n");
			if(!out.equals("Error\n"))
				throw new Exception();
			
		}catch(Exception e){
			eCaught = true;
		}
		
		if(!eCaught){
			score+= Breakdown.s2tab;
			SC.score.append("\n\t select on two tables without where clause works - " + Breakdown.s2tab + "/" + Breakdown.s2tab);
		}else
			SC.score.append("\n\t select on two tables without where clause doesn't work - 0/" + Breakdown.s2tab);
	}
	
	
	@Test 
	public void twoTableSelectWithWhere(){
		boolean eCaught = false;
		String out = null;
		try{
			Main m = new Main();
			m.process("@select.sql");

			out = m.process("select cast.actor from movie, cast where movie.title = cast.title and movie.title = 'Cast Away';\n");
						
			Set<Map<String, String>> ref = new HashSet<Map<String, String>>();
			Set<Map<String, String>> actual;
			actual = parseResults(out);
			
			Map<String, String> tmp = new HashMap<String, String>();
			tmp.put("cast.actor", "Paul Sanchez");
			ref.add(tmp);
			tmp = new HashMap<String, String>();
			tmp.put("cast.actor", "Lari White");
			ref.add(tmp);
			tmp = new HashMap<String, String>();
			tmp.put("cast.actor", "Leonid Citer");
			ref.add(tmp);
			tmp = new HashMap<String, String>();
			tmp.put("cast.actor", "Tom Hanks");
			ref.add(tmp);
			
			if(!actual.equals(ref))
				throw new Exception();
			
			out = m.process("select movie.actor from movie, cast where movie.title = cast.title and movie.title = 'Cast Away';\n");
			if(!out.equals("Error\n"))
				throw new Exception();
			
			
			out = m.process("select * from movie, cast where year = movie.id;\n");
			if(countResults(out) != 312)
				throw new Exception();
			
			
			out = m.process("select actor from movie, cast where movie.title = cast.title and cast.actor = 'Harrison Ford';\n");
			if(!out.split("\nplan:")[0].equals("1:\nactor: Harrison Ford\n\n2:\nactor: Harrison Ford\n\n3:\nactor: Harrison Ford\n\n4:\nactor: Harrison Ford\n\n" +
					"5:\nactor: Harrison Ford\n"))
				throw new Exception();
			
			out = m.process("  select * from movie, cast WHERE cast.title = movie.title and dir = 'Steven Spielberg' and cast.title = movie.title ;\n");
			if(countResults(out) != 22)
				throw new Exception();
			
			out = m.process("select * from movie, cast where cast.title = movie.title and dir = 'Steven Spielberg' and title = movie.title ;\n");
			if(!out.equals("Error\n"))
				throw new Exception();
					
		}catch(Exception e){
			eCaught = true;
		}
		
		if(!eCaught){
			score+= Breakdown.s2tabWhere;
			SC.score.append("\n\t select on two tables with where clause works - " + Breakdown.s2tabWhere + "/" + Breakdown.s2tabWhere);
		}else
			SC.score.append("\n\t select on two tables with where clause doesn't work - 0/" + Breakdown.s2tabWhere);
	}
	
	
	private static  Set<Map<String, String>> parseResults(String in){
		in = "\n" + in;
		Pattern p = Pattern.compile("\n[0-9]*:\n");
		String res = (in.split("plan:"))[0];		
		String[] recs = p.split(res);
		Set<Map<String, String>> results = new HashSet<Map<String, String>>();
		for(String r : recs){
			if(r.length()>0)
				results.add(getRecord(r.trim()));
		}
		return results;
	}
	
	private static int countResults(String in){
		in = "\n" + in;
		Pattern p = Pattern.compile("\n[0-9]*:\n");
		String res = (in.split("plan:"))[0];
		String[] recs = p.split(res);
		return recs.length-1;
	}
	
	private static  Map<String, String>  getRecord(String in){
		Map<String, String> r = new HashMap<String, String>();
		String[] lines = in.split("\n");
		for(String line : lines){
			String[] parts = line.split(": ");
			r.put(parts[0], parts[1]);
		}
		return r;
	}
	
	
}
